﻿/* 
 * Since 2008 - 2012
 */

package com.hh.bss.customer.dao.impl;

import java.util.*;

import com.hh.bss.auth.entity.SystemUser;
import com.hh.bss.auth.util.AuthSqlUtil;
import com.hh.bss.base.*;
import com.hh.bss.common.ConstantDic;
import com.hh.bss.common.MapObjectRowMapper;
import com.hh.bss.common.cache.SystemUserCache;
import com.hh.bss.common.page.*;
import com.hh.bss.common.page.impl.*;
import com.hh.bss.customer.dao.*;
import com.hh.bss.customer.entity.*;
import com.hh.bss.customer.service.*;
import com.hh.bss.customer.vo.query.*;
import com.hh.bss.util.*;





import static com.hh.bss.util.ObjectUtils.*;

import org.springframework.stereotype.Repository;

@Repository
public class CustomerContacterDaoImpl extends BaseHibernateDao<CustomerContacter,java.lang.Integer>  implements CustomerContacterDao<CustomerContacter,java.lang.Integer>{

	public Class getEntityClass() {
		return CustomerContacter.class;
	}
	public List findCustomContacter(CustomerContacterQuery query){
		StringBuffer sql = new StringBuffer();
		List values = new ArrayList();
		//
		sql.append(" select * from ( ");
		sql.append(" select a.cust_id custId,a.role,a.cv_id as cvId,b.cv_name as cvName,b.cv_name_en cvNameEn,b.email from customer_contacter a,cv b where  a.cv_id=b.id");
		sql.append(" ) t where 1=1 ");
		
		//a.cust_id=? and a.role=? and
		if (isNotEmpty(query.getCustId())){
			 //增加合同服务公司共享联系人
			sql.append(" and ( t.custId=? ");
			sql.append(" or exists(select 1 from customer_contract a, customer_contract_service_company b where a.cust_id=t.custId and a.id=b.contract_id  and b.cust_id=?) ");
			sql.append(" )");
			values.add(query.getCustId());
			values.add(query.getCustId());
		}
		if (isNotEmpty(query.getRole())){
			sql.append(" and t.role=? ");	
			values.add(query.getRole());
		}
		
		return super.getJdbcDao().findByQuery(sql.toString(),values.toArray(), new MapObjectRowMapper());
	}
	public Page findPage(CustomerContacterQuery query) {
        //XsqlBuilder syntax,please see http://code.google.com/p/rapid-xsqlbuilder
        // [column]为字符串拼接, {column}为使用占位符. [column]为使用字符串拼接,如username='[username]',偷懒时可以使用字符串拼接 
        // [column] 为PageRequest的属性
        
		
		    Class[] os={CustomerContacterQuery.class};
    		Class property= org.springframework.beans.BeanUtils.findPropertyType(query.getField_type(), os);
        if (property!=null){
        	 if ("java.lang.String".equals(property.getName())){
	    			
	    			com.hh.bss.util.BeanUtils.setProperty(query, query.getField_type(),query.getQuery());
	    			
	    		}
	    		else if ("java.lang.Integer".equals(property.getName())){
	    			
	    			com.hh.bss.util.BeanUtils.setProperty(query, query.getField_type(), StringHelper.parseInteger(query.getQuery(), 0));
	    			
	    		}
	    		else if ("java.lang.Double".equals(property.getName())){
	    			com.hh.bss.util.BeanUtils.setProperty(query, query.getField_type(), StringHelper.parseDouble(query.getQuery(), 0));
	    		}
	    		else if ("java.util.Date".equals(property.getName())){
	    			com.hh.bss.util.BeanUtils.setProperty(query, query.getField_type(), DateHelper.toDate(query.getQuery(), DateHelper.DEFAULT_DATE_FORMAT));
	    		}
        }	 
        //生成sql2的原因是为了不喜欢使用xsqlbuilder的同学，请修改生成器模板，删除本段的生成
        StringBuffer sql2 = new StringBuffer();
        List values = new ArrayList();
        sql2.append("select * from ( ");
        sql2.append(" select a.id,a.cust_id custId,a.role,a.cv_id cvId,b.cv_name as cvName,b.cv_name_en cvNameEn,a.comment,a.creator,a.create_dt createDt,a.modifier,a.modified_dt modifiedDt, ");
        sql2.append(" b.mobile,b.tel,b.title,b.email,b.dept,d.name custName ");
        sql2.append(" from customer_contacter a inner join cv b on a.cv_id = b.id  ");
        sql2.append(" left join customer c on c.id = a.cust_id ");
        sql2.append(" left join company d on c.company_id = d.id ");
        sql2.append(" ) t where 1=1 ");
        
        if (isNotEmpty(query.getCustId())){
        	sql2.append(" and t.custId=? ");	
			values.add(query.getCustId());	
		}
        if (isNotEmpty(query.getRole())){
			sql2.append(" and t.role=? ");	
			values.add(query.getRole());
		}
        if (isNotEmpty(query.getCvName())){
        	sql2.append(" and t.cvName like ? ");	
			values.add("%"+query.getCvName()+"%");	
		}
        
        if (query.getOnlineUser()!=null){
  			String authsql=AuthSqlUtil.authCustomerProtectSQL(query.getOnlineUser().getUsername(),"t.custId", "t.creator");
  			if (!StringHelper.isEmpty(authsql)) sql2.append(authsql);			
  	    }
        
        return super.getJdbcDao().createPage(sql2.toString(),values.toArray(), new MapObjectRowMapper(), query.getPageSize(), query.getPageNumber());
	}
	private String authSQL(String username,String creatorColumn){
		//admin不受控制
		if (ConstantDic.ADMIN_USER.equals(username)) return "";
		
		SystemUser systemUser=SystemUserCache.getSystemUserByUsername(username);
		
		StringBuffer sb=new StringBuffer();
		
		//Account 角色 看全部
        //GM 看全部
		if (SystemUserCache.isRole(systemUser.getId(), ConstantDic.ROLE_GM)) return "";

		
		 	
		sb.append(" and (");
		//自己的
		sb.append(creatorColumn).append("='").append(username).append("'");
		//客户职位相关人 看属于自己的职位客户的
		sb.append(" or exists ( select 1 from contract_job t1,contract_job_partner t2 where  t1.cust_id=(select max(t3.id) from customer t3 where t3.company_id=t.companyId) and t2.role in('"+ConstantDic.JOB_PARTNER_TYPE_MC+"','"+ConstantDic.JOB_PARTNER_TYPE_PC+"') and t1.id=t2.job_id and t2.account= '").append(username).append("' ) ");
		//自己下级创建的
		sb.append(" or ").append(creatorColumn).append(" in ( select b3.account from system_user b3 where title_id in(select b1.id from system_branch_title b1 where b1.parent_ids like concat((select bb2.parent_ids from system_user b2,system_branch_title bb2 where bb2.id=b2.title_id and b2.account='").append(username).append("'),'%') and b1.id<>(select bb3.title_id from system_user bb3 where bb3.account='").append(username).append("')) )");
		
		//下级能看到的
		//select b3.account from system_user b3 where title_id in(select b1.id from system_branch_title b1 where b1.id in(select b2.title_id from system_user b2 where b2.account='").append(username).append("'))
		//select b3.account from system_user b3 where title_id in(select b1.id from system_branch_title b1 where b1.parent_ids like concat((select bb2.parent_ids from system_user b2,system_branch_title bb2 where bb2.id=b2.title_id and b2.account='").append(username).append("'),'%') and b1.id<>(select bb3.title_id from system_user bb3 where bb3.account='").append(username).append("'))
		sb.append(" or exists ( select 1 from contract_job t1,contract_job_partner t2 where  t1.cust_id=(select max(t3.id) from customer t3 where t3.company_id=t.companyId) and t2.role in('"+ConstantDic.JOB_PARTNER_TYPE_MC+"','"+ConstantDic.JOB_PARTNER_TYPE_PC+"') and t1.id=t2.job_id and t2.account in (").append(" select b3.account from system_user b3 where title_id in(select b1.id from system_branch_title b1 where b1.parent_ids like concat((select bb2.parent_ids from system_user b2,system_branch_title bb2 where bb2.id=b2.title_id and b2.account='").append(username).append("'),'%') and b1.id<>(select bb3.title_id from system_user bb3 where bb3.account='").append(username).append("')) ").append(" ) ) ");
		
		sb.append(" )");
		
		return sb.toString();
		
	}
	public Integer getCustIdByCompanyId(Integer companyId) {
		if(companyId != null && companyId>0){
			String query = "select a.id custId from customer a where a.company_id = " + companyId;
			@SuppressWarnings("unchecked")
			List<HashMap<String, Integer>> custList = this.getJdbcDao().findByQuery(query, new MapObjectRowMapper());
			if(custList.size() >0){
				Integer x = custList.get(0).get("id");
				return custList.get(0).get("id");
			}
					
		}
		return null;
		
	}
	/**
	 * 检查是否存在同类型客户联系人
	 */
	public int checkCustomerRoleCount(String custId, String role) {
		int count  = -1;
		if(isNotEmpty(custId) && isNotEmpty(role)){
			List<String> values = new ArrayList<String>();
			String query  = "select count(1) from customer_contacter a where a.cust_id = ? and a.role = ? ";
			values.add(custId);
			values.add(role);
			count  =  this.getJdbcDao().getJdbcTemplate().queryForInt(query, values.toArray()); 
		}
		return count;
	}

}
